* Set current directory and open log
cd "E:\Seagate Sync\VOL\Personal folder\Documents\Research\Current projects\Working party meetings\Posted\EUP 160404"
capture log close
log using "Data management\wpar-management02-council", replace text


***********************************************************************************
* Coding of Council meetings and generation of meeting level and aggregate datasets
***********************************************************************************


* Project and author information
********************************
* Programme:	wpar-management02-council.do
* Date:			4 April 2016
* Author:		Frank Haege, University of Limerick
* Contact:		frank.haege@ul.ie 

* Do-file description
*********************
* Relying on the annual meeting calendars supplied by the Council Secretariat in response
* to a request for access to documents, this do-file generates a dataset with information 
* on the dates and duration (in half-days) of Council meetings between 1996 and 2014.


* Clear memory  
**************
version 12.0
clear all
macro drop _all
set more off

* Load and append dataset with information on dates and duration of working party meetings
use "Input data\wpar-rawdata-council-1996-2014.dta", clear

* Describe data set
describe, d

* Rename and label meeting description variables
rename meetingdescription description
label var description "Meeting description"
* Remove leading and trailing blanks
replace description = trim(description)
* Collapse multiple internal blanks
replace description = itrim(description)

* Generate date of meeting variable
generate date = date(sessiondate, "DMY")
format date %d
label var date "Meeting date"
tab date, m

* Generate categorical time-slot variable
generate slot = .
replace slot = 1 if slots == "AM"
replace slot = 2 if slots == "PM"
replace slot = 3 if slots == "EV"
replace slot = 4 if slots == "AM-PM"
replace slot = 5 if slots == "PM-EV"
replace slot = 6 if slots == "AM-EV"
replace slot = 7 if slots == "AM-PM-EV"
label var slot "Meeting time slot"
label def slotl 1 "AM" 2 "PM" 3 "EV" 4 "AM-PM" 5 "PM-EV" 6 "AM-EV" 7 "AM-PM-EV"
label val slot slotl
tab slots, m
tab slot, m

* Drop variables that are not needed
keep date slot description
order date slot description

* Generate variable holding original meeting description
generate description_orig = description
label var description_orig "Meeting description (original)"


***************************************************************
* Standardize meeting description to make coding more efficient
***************************************************************

* Insert blanks around slash
list date description if regexm(description, "\+")
list description if regexm(description, "(.*[A-Z])( ?\/ ?)([A-Z].*)")
replace description = regexs(1)+" / "+regexs(3) if regexm(description, "(.*[A-Z])( ?\/ ?)([A-Z].*)")

* Replace abbreviated Council name parts with full name
replace description = subinstr(description, "AFF.", "AFFAIRES", .)
replace description = subinstr(description, "POLIT.", "POLITIQUE", .)
replace description = subinstr(description, "POLITIQ.", "POLITIQUE", .)
replace description = subinstr(description, "POL.", "POLITIQUE", .)
replace description = subinstr(description, "SOC.", "SOCIALE", .)
replace description = subinstr(description, "INT.", "INTERIEURES", .)
replace description = subinstr(description, "INTER.", "INTERIEURES", .)
replace description = subinstr(description, "CIV.", "CIVILE", .)
replace description = subinstr(description, "PROT.", "PROTECTION", .)
replace description = subinstr(description, "PROTECT.", "PROTECTION", .)
replace description = subinstr(description, "PROTEC.", "PROTECTION", .)
replace description = subinstr(description, "CONSOM.", "CONSOMMATEURS", .)
replace description = subinstr(description, "CONSOMM.", "CONSOMMATEURS", .)
replace description = subinstr(description, "GEN.", "GENERALES", .)
replace description = subinstr(description, "REL.", "RELATIONS", .)
replace description = subinstr(description, "RELAT.", "RELATIONS", .)
replace description = subinstr(description, "EXT.", "EXTERIEURES", .)
replace description = subinstr(description, "COMP.", "COMPETITIVITE", .)
replace description = subinstr(description, "COMPETIT.", "COMPETITIVITE", .)
replace description = subinstr(description, "COMPET.", "COMPETITIVITE", .)
replace description = subinstr(description, "RECH.", "RECHERCHE", .)
replace description = subinstr(description, "EDUC.", "EDUCATION", .)
replace description = subinstr(description, "ECON.", "ECONOMIQUES", .)
replace description = subinstr(description, "FIN.", "FINANCIERES", .)
replace description = subinstr(description, "FINANC.", "FINANCIERES", .)
replace description = subinstr(description, "DEVELOP.", "DEVELOPPEMENT", .)
replace description = subinstr(description, "TRANSP.", "TRANSPORTS", .)
replace description = subinstr(description, "JEUN.", "JEUNESSE", .)
replace description = subinstr(description, "INDUST.", "INDUSTRIE", .)
replace description = subinstr(description, "IND.", "INDUSTRIE", .)
replace description = subinstr(description, "TELECOMS.", "TELECOMMUNICATIONS", .)
replace description = subinstr(description, "TELECOM.", "TELECOMMUNICATIONS", .)
replace description = subinstr(description, "TRAV.", "TRAVAIL", .)
replace description = subinstr(description, "CONS.", "CONSOMMATEURS", .)
replace description = subinstr(description, "RECH.", "RECHERCHE", .)
replace description = subinstr(description, "ENVIR.", "ENVIRONNEMENT", .)
replace description = subinstr(description, "AFFAIRES GEN/", "AFFAIRES GENERALES /", .)
replace description = subinstr(description, "A.G.", "AFFAIRES GENERALES", .)
replace description = subinstr(description, "AFF .ECONOMIQUES ET FINANCIERES", "AFFAIRES ECONOMIQUES ET FINANCIERES", .)
replace description = subinstr(description, "POLITIQUE SOC,", "POLITIQUE SOCIALE,", .)
replace description = subinstr(description, "POLITIQUESOC,", "POLITIQUE SOCIALE,", .)

* Correct spelling mistakes
replace description = subinstr(description, "JUSTICE / AFFAIRES INTERIEURES", "JUSTICE/INTERIEUR", .)
replace description = subinstr(description, "RELATIONSEXTERIEURES", "RELATIONS EXTERIEURES", .)
replace description = subinstr(description, "AFFAIRESGENERALES", "AFFAIRES GENERALES", .)
replace description = subinstr(description, "JUSTICE, AFFAIRESINTERIEURES", "JUSTICE ET AFFAIRES INTERIEURES", .)
replace description = subinstr(description, "JUSTICE ET AFFAIRESINTERIEURESET PROTECTIONCIVIL", "JUSTICE ET AFFAIRES INTERIEURES ET PROTECTION CIVIL", .)
replace description = subinstr(description, "JUSTICE, AFFAIRESINTERIEURES", "JUSTICE ET AFFAIRES INTERIEURES", .)
replace description = subinstr(description, "AFFAIRES ECONOMIQUES FINANCIERES", "AFFAIRES ECONOMIQUES ET FINANCIERES", .)
replace description = subinstr(description, "JUSTICE, AFFAIRES INTERIEURES ET PROTECTIONCIVILE", "JUSTICE/AFFAIRES INTERIEURES ET PROTECTION CIVILE", .)
replace description = subinstr(description, "JUSTICE, AFFAIRESINTERIEURES", "JUSTICE ET AFFAIRES INTERIEURES", .)
replace description = subinstr(description, "TRANSPORTSTELECOM , ENERGIE", "TRANSPORTS, TELECOM ET ENERGIE", .)
replace description = subinstr(description, "CONSOMMATEURS AFFAIRES GENERALES ET RELATIONS EXTERIEURES", "AFFAIRES GENERALES ET RELATIONS EXTERIEURES", .)
* according to press release, this was just a General Affairs and External Relations meeting
replace description = subinstr(description, "POLITIQUESOCIALE", "POLITIQUE SOCIALE", .)
replace description = regexr(description, "SANTE ?, ?", "SANTE ET ")
list description if regexm(description, "SANTE ?, ?[A-Z]")

*************************************************
* Code Council formation from meeting description
*************************************************

* Generate and label Council formation variable
generate cformo = .
label var cformo "Council formation (original)"

* Explanation of numerical code:
* First digit identifies one of nine formations in 2002-2009 structure
* Second digit identifies formation in 2000-2002 structure
* Third digit identifies formation in pre-2002 structure

* Education, Youth, and Culture
replace cformo = 121 if regexm(description, "EDUCATION")
replace cformo = 122 if regexm(description, "JEUNESSE")
replace cformo = 111 if regexm(description, "EDUCATION ET JEUNESSE") /*
	*/ | regexm(description, "EDUCATION / JEUNESSE")
replace cformo = 112 if regexm(description, "CULTURE")
replace cformo = 100 if regexm(description, "EDUCATION, JEUNESSE ET CULTURE") /*
	*/ | regexm(description, "EDUCATION, JEUNESSE, CULTURE")

* Agriculture and Fisheries
replace cformo = 211 if regexm(description, "AGRICULTURE") /*
	*/ | regexm(description, "AGRI")
replace cformo = 212 if regexm(description, "PECHE")
replace cformo = 200 if regexm(description, "AGRICULTURE ET PECHE") /*
	*/ | regexm(description, "AGRI / PECHE")
	
* Economic and Financial Affairs
replace cformo = 311 if regexm(description, "BUDGET")
replace cformo = 300 if regexm(description, "AFFAIRES ECONOMIQUES ET FINANCIERES") /*
	*/ | regexm(description, "ECOFIN")
	
* General Affairs and External Relations
replace cformo = 411 if regexm(description, "DEVELOPPEMENT")
replace cformo = 400 if regexm(description, "AFFAIRES GENERALES") /*
	*/ | regexm(description, "RELATIONS EXTERIEURES") /*
	*/ | regexm(description, "MINISTRES DEFENSE") /*
	*/ | regexm(description, "AGRE") /*
	*/ | regexm(description, "CHEFS D'ETAT") /*
	*/ | regexm(description, "COMMERCE")	
* Heads of State or Government meetings with session number are coded as General Affairs Council meetings
replace cformo = 400 if regexm(description, "AFFAIRES ETRANGERES")

* Environment
replace cformo = 500 if regexm(description, "ENVIRONNEMENT")

* Justice and Home Affairs
replace cformo = 621 if regexm(description, "PROTECTION CIVILE")
replace cformo = 600 if regexm(description, "JUSTICE ET AFFAIRES INTERIEURES") /*
	*/ | regexm(description, "JUSTICE/INTERIEUR") /*
	*/ | regexm(description, "JUSTICE / INTERIEUR") /*
	*/ | regexm(description, "JAI") /*
	*/ | regexm(description, "JUSTICE, AFFAIRES INTERIEURES") /*
	*/ | regexm(description, "JUSTICE/AFFAIRES INTERIEURES")

* Transport, Telecommunications, and Energy
replace cformo = 721 if regexm(description, "TRANSPORTS")
replace cformo = 722 if regexm(description, "TELECOMMUNICATIONS")
replace cformo = 723 if regexm(description, "INDUSTRIE")
replace cformo = 724 if regexm(description, "ENERGIE")
replace cformo = 711 if regexm(description, "TRANSPORTS ET TELECOMMUNICATIONS")
replace cformo = 712 if regexm(description, "INDUSTRIE ET ENERGIE")
replace cformo = 700 if regexm(description, "TRANSPORTS, TELECOM ET ENERGIE") /*
	*/ | regexm(description, " TTE")
	
* Competitiveness
replace cformo = 821 if regexm(description, "MARCHE INTERIEUR")
replace cformo = 822 if regexm(description, "TOURISME")
replace cformo = 823 if regexm(description, "CONSOMMATEURS")
replace cformo = 811 if regexm(description, "MARCHE INTERIEUR, CONSOMMATEURS ET TOURISME") /*
	*/ | regexm(description, "MARCHE INTERIEUR / CONSOMMATEURS / ?TOURISME") /*
	*/ | regexm(description, "MARCHE INTERIEUR, CONSOMMATEURS / TOURISME") /*
	*/ | regexm(description, "MARCHE INTERIEURES, CONSOMMATEURS / TOURISME") /*
	*/ | regexm(description, "MARCHE INTERIEURES, CONSOMMATEURS ET TOURISME")
replace cformo = 812 if regexm(description, "RECHERCHE")
replace cformo = 800 if regexm(description, "COMPETITIVITE") /*
	*/ | regexm(description, "ESPACE") /*
	*/ | regexm(description, "CONJOINT UE/ASE") /*
	*/ | regexm(description, "CONJOINT UE / ASE")
	
* Employment, Social Policy, Health, and Consumer Protection
replace cformo = 911 if regexm(description, "EMPLOI ET POLITIQUE SOCIALE") /*
	*/ | regexm(description, "TRAVAIL") | regexm(description, "AFFAIRES SOCIALES")
replace cformo = 912 if regexm(description, "SANTE")
replace cformo = 900 if regexm(description, "EMPLOI, POLITIQUE SOCIALE, SANTE ET CONSOMMATEURS") /*
	*/ | regexm(description, "EMPLOI, POLITIQUE SOCIALE SANTE ET CONSOMMATEURS") /*
	*/ | regexm(description, "CONJOINT UE/ASE")

* Define value labels of Council formation variable
label def cnamel /*
	*/ 121 "Education" /*
	*/ 122 "Youth" /*
	*/ 111 "Education and Youth" /*
	*/ 112 "Culture and Audiovisual Services" /*
	*/ 100 "Education, Youth, and Culture" /*
	*/ 211 "Agriculture" /*	
	*/ 212 "Fisheries" /*	
	*/ 200 "Agriculture and Fisheries" /*	
	*/ 311 "Budget" /*	
	*/ 300 "Economic and Financial Affairs" /*	
	*/ 411 "Development" /*	
	*/ 400 "General Affairs and External Relations" /*	
	*/ 500 "Environment" /*
	*/ 621 "Civil Protection" /*
	*/ 600 "Justice and Home Affairs" /*
	*/ 500 "Environment" /*
	*/ 721 "Transport" /*
	*/ 722 "Telecommunications" /*
	*/ 723 "Industry" /*
	*/ 724 "Energy" /*
	*/ 711 "Transport and Telecommunications" /*
	*/ 712 "Industry and Energy" /*
	*/ 700 "Transport, Telecommunications, and Energy" /*
	*/ 821 "Internal Market" /*
	*/ 822 "Tourism" /*
	*/ 823 "Consumer Affairs" /*
	*/ 811 "Internal Market, Consumer Affairs, and Tourism" /*
	*/ 812 "Research" /*
	*/ 800 "Competitiveness" /*
	*/ 911 "Employment and Social Policy" /*
	*/ 912 "Health" /*
	*/ 900 "Employment, Social Policy, Health, and Consumer Affairs" /*
	*/ , modify

* Assign value labels to Council formation variable
label val cformo cnamel
tab cformo, m
sort cformo
order date slot cformo 

* Drop observations that do not describe a Council meeting
sort description date
list description date if cformo == . 
* Make corrections to coding
replace cformo = 400 if regexm(description, "Sta. Maria da Feira: 2280EME CONSEIL EUROPEEN")
* This is a General Affairs Council meeting
replace cformo = . if description == "GR. MIXTE DEVELOPPEMENT / ENVIRONNEMENT"
* no such Council meeting on that day according to press releases
replace cformo = . if description == "CONFERENCE UE (MIN. INDUSTRIE)/PAYS MED."
replace cformo = . if description == "RENCONTRE AGRI / ACP-PMA"
replace cformo = . if description == "H.N. DEVELOPPEMENT / ENVIRONNEMENT"
replace cformo = . if description == "Luxembourg : ECOFIN-EUROMED MINISTERIELLE FEMIP"
list date description if cformo != . & regexm(description, "TROIKA")
replace cformo = . if cformo != . & regexm(description, "TROIKA") 
list date description_orig if cformo == .
tab description_orig if cformo == ., m
drop if cformo == .
* 108 observations deleted


********************************************************************
* Check and make corrections to coding of Council formation variable
********************************************************************

* Sort data by Council formation and date
sort cformo date

* Education, Youth and Culture
tab description if cformo == 121
list description if cformo == 121
tab description if cformo == 122
tab description if cformo == 111
tab description if cformo == 112
list date description if cformo == 112
tab description if cformo == 100

* Agriculture and Fisheries
tab description if cformo == 211
list description if cformo == 211
tab description if cformo == 212
tab description if cformo == 200
list description if cformo == 200

* Economic and Financial Affairs
tab description if cformo == 311
tab description if cformo == 300
list date description if cformo == 300

* General Affairs and External Relations
tab description if cformo == 411
list date description if cformo == 411
tab description if cformo == 400
list slot date description if cformo == 400

* Environment
tab description if cformo == 500
list date description if cformo == 500

* Justice and Home Affairs
tab description if cformo == 621
tab description if cformo == 600
list date description if cformo == 600
* Add additional observations for joint meetings
list date description if cformo == 600 & regexm(description, "CONJ.")
expand 2 if cformo == 600 & regexm(description, "CONJ."), generate(newobs)
replace cformo = 300 if newobs == 1
drop newobs

* Transport, Telecommunications, and Energy
tab description if cformo == 721
list date description if cformo == 721
* Add additional observations for joint meetings
list date description if cformo == 721 & regexm(description, "CONJ.")
expand 2 if cformo == 721 & regexm(description, "CONJ."), generate(newobs)
replace cformo = 500 if newobs == 1
drop newobs
tab description if cformo == 722
list date description if cformo == 722
tab description if cformo == 723
list date description if cformo == 723
tab description if cformo == 724
list date description if cformo == 724
tab description if cformo == 711
list date description if cformo == 711
tab description if cformo == 712
list date description if cformo == 712
tab description if cformo == 700
list date description if cformo == 700

* Competitiveness
tab description if cformo == 821
list date description if cformo == 821
tab description if cformo == 822
list date description if cformo == 822
tab description if cformo == 823
list date description if cformo == 823
tab description if cformo == 811
list date description if cformo == 811
tab description if cformo == 812
list date description if cformo == 812
tab description if cformo == 800
list date description if cformo == 800
* Space Council meetings will be dropped when duplicate observations are being dropped

* Employment, Social Policy, Health, and Consumer Protection
tab description if cformo == 911
list date description if cformo == 911
* Add additional observations for joint meetings
list date description if cformo == 911 & regexm(description, "ECOFIN")
expand 2 if cformo == 911 & regexm(description, "ECOFIN"), generate(newobs)
replace cformo = 300 if newobs == 1
drop newobs
* Add additional observations for joint meetings
list date description if cformo == 911 & regexm(description, "JUSTICE")
expand 2 if cformo == 911 & regexm(description, "JUSTICE"), generate(newobs)
replace cformo = 600 if newobs == 1
drop newobs
* Add additional observations for joint meetings
list date description if cformo == 911 & regexm(description, "EDUCATION")
expand 2 if cformo == 911 & regexm(description, "EDUCATION"), generate(newobs)
replace cformo = 121 if newobs == 1
drop newobs
tab description if cformo == 912
list date description if cformo == 912
tab description if cformo == 900
list date description if cformo == 900

* Generate period variable for different organizational structures
generate period = .
replace period = 1 if date < d(01jul2000)
replace period = 2 if date >= d(01jul2000) & date < d(01aug2002)
replace period = 3 if date >= d(01aug2002) & date < d(01nov2009)
replace period = 4 if date >= d(01nov2009) & date != .
label var period "Period of Council formation structure"
label def periodl /*
	*/ 1 "Before 01/07/2000" /*
	*/ 2 "01/07/2000 - 31/07/2002" /*
	*/ 3 "01/08/2002 - 31/10/2009" /*
	*/ 4 "After 01/11/2009" /*
	*/ , modify
label val period periodl
tab period, m
order period date slot cformo 
gsort -cformo date

* Generate variable indicating period according to Council formation code
tostring(cformo), generate(cname1) 
generate period1 = .
replace period1 = real(substr(cname1, 2, 1))
order period period1

* Correct observations in post-2002 period if they are coded as pre-2002 period configurations
list period period1 date slot cformo if (period == 3 | period == 4) & period1 != 0
replace cformo = 900 if cformo == 911 & period == 3 & period1 != 0
replace cformo = 400 if cformo == 411 & period == 3 & period1 != 0
replace cformo = 200 if cformo == 211 & period == 3 & period1 != 0
replace cformo = 700 if cformo == 721 & period == 4 & period1 != 0

* Correct observations in post-2000 period if they are coded as pre-2000 period configurations
list period period1 date slot cformo if period == 2 & period1 == 2
replace cformo = 711 if (cformo == 721 | cformo == 722) & period == 2 & period1 == 2
replace cformo = 811 if cformo == 821 & period == 2 & period1 == 2
replace cformo = 712 if (cformo == 724 | cformo == 723) & period == 2 & period1 == 2

* Check consistency again
drop cname1 period1
tostring(cformo), generate(cname1) 
generate period1 = .
replace period1 = real(substr(cname1, 2, 1))
tab cformo period if period1 == 0
tab cformo period if period1 == 1
tab cformo period if period1 == 2
drop period1 cname1

* Generate meeting number variable
generate cnum = .
replace cnum = real(regexs(0)) if (regexm(description, "[0-9][0-9][0-9][0-9]"))
* Manual check and correction
list date description cformo cnum if cnum == 3850 & date == d(03oct2000)
replace cnum = 2293 if cnum == 3850 & date == d(03oct2000)
list date description cformo cnum if cnum == 2387 & date == d(17dec2013)
replace cnum = 3287 if cnum == 2387 & date == d(17dec2013)
label var cnum "Meeting number"
tab cnum, m
list date slot description_orig cformo cnum if cnum == .
drop if description_orig == "BRIEFING CONSEIL EDUCATION, JEUNESSE ET CULTURE"
drop if description_orig == "RENC. PRESIDENT CONSEIL AGRI - COPA"
* The other meetings missing a meeting number are either providing no information
* or are extraordinary meetings

* Identify and drop duplicates with the same meeting numbers
gsort cformo date
duplicates report date slot cformo cnum
duplicates tag date slot cformo cnum, gen(duplicate)
list date slot cformo description cnum duplicate if duplicate > 0
duplicates drop date slot cformo cnum, force
drop duplicate
* 19 observations dropped

* Identify and drop duplicates with different meeting numbers
gsort cformo date -cnum
duplicates report date slot cformo 
duplicates tag date slot cformo, gen(duplicate)
list date slot cformo description cnum duplicate if duplicate > 0
list date slot cformo description cnum duplicate if date == d(17jun1998)
* Drop duplicate observations with missing meeting numbers
generate nonumber = 1 if duplicate == 1 & cnum == .
list date slot cformo description cnum nonumber duplicate if duplicate > 0 & nonumber == 1
drop if nonumber == 1
* 19 observations deleted
drop duplicate

* Drop remaining duplicate observations
gsort cformo date -cnum
duplicates tag date slot cformo, gen(duplicate)
list date slot cformo description cnum duplicate if duplicate > 0
* These observations have the correct meeting numbers, but not removing one of the observations
* would lead to double-counting the meeting duration
duplicates drop date slot cformo, force
* 14 observations deleted
drop duplicate
duplicates report date slot cformo

* Check observations with missing meeting number
tab description if cnum == .
sort date description
list date description if cnum == .

* Generate time slot dummy variables
tab slot, m
generate am = 0
replace am = 1 if slot == 1 | slot == 4 | slot == 6 | slot == 7
label var am "Morning (yes/no)"
generate pm = 0
replace pm = 1 if slot == 2 | slot == 4 | slot == 5 | slot == 7
label var pm "Afternoon (yes/no)"
generate ev = 0
replace ev = 1 if slot == 3 | slot == 5 | slot == 6 | slot == 7
label var ev "Evening (yes/no)"
label def yesno 0 "No" 1 "Yes"
label val am yesno
label val pm yesno
label val ev yesno
tab slot am, m
tab slot pm, m
tab slot ev, m


*************************************************************
* Collapse observations if they have meetings on the same day
*************************************************************

* Merge observations with meeting on same day in the morning
gsort date cformo -slot cnum 
duplicates report date cformo am
duplicates list date cformo am 
duplicates tag date cformo am, gen(duplicate)
list date slot cformo description cnum ev am pm if duplicate == 1 & am == 1
replace cnum = 2396 if cnum == . & date == d(07dec2001) & description == "CONSEIL JUSTICE, AFFAIRES INTERIEURESET PROTECTION CIVILE / CTE MIXTE"
duplicates drop date cformo am if am == 1, force
* 17 observations deleted
drop duplicate

* Merge observations with meeting on same day in the afternoon
gsort date cformo -slot cnum 
duplicates report date cformo pm
duplicates tag date cformo pm, gen(duplicate)
list date slot cformo description cnum ev am pm if duplicate == 1 & pm == 1
duplicates drop date cformo pm if pm == 1, force
* 15 observations deleted
drop duplicate

* Merge observations with meeting on same day in the evening
gsort date cformo -slot cnum 
duplicates report date cformo ev
duplicates tag date cformo ev, gen(duplicate)
list date slot cformo description cnum ev am pm if duplicate == 1 & ev == 1
drop duplicate

* Merge observations with meetings on same day, one in the morning and one in the afternoon and evening
gsort date cformo cnum -slot  
duplicates report date cformo if am == 1 | (pm == 1 & ev == 1)
duplicates tag date cformo if am == 1 | (pm == 1 & ev == 1), gen(duplicate)
list date slot cformo description cnum ev am pm if duplicate == 1
replace slot = 7 if duplicate == 1
replace ev = 1 if duplicate == 1
replace am = 1 if duplicate == 1
replace pm = 1 if duplicate == 1
duplicates drop date cformo if am == 1 | (pm == 1 & ev == 1), force
* 3 observations deleted
drop duplicate

* Merge observations with meetings on same day, one in the evening and one in the morning and afternoon
gsort date cformo cnum -slot  
duplicates report date cformo if ev == 1 | (pm == 1 & am == 1)
duplicates tag date cformo if ev == 1 | (pm == 1 & am == 1), gen(duplicate)
list date slot cformo description cnum ev am pm if duplicate == 1
replace slot = 7 if duplicate == 1
replace ev = 1 if duplicate == 1
replace am = 1 if duplicate == 1
replace pm = 1 if duplicate == 1
duplicates drop date cformo if ev == 1 | (pm == 1 & am == 1), force
* 1 observation deleted
drop duplicate

* Merge observations with meetings on same day, one in the afternoon and one in the evening
gsort date cformo cnum -slot  
duplicates report date cformo if pm == 1 | ev == 1
duplicates tag date cformo if pm == 1 | ev == 1, gen(duplicate)
list date slot cformo description cnum ev am pm if duplicate == 1
replace slot = 5 if duplicate == 1
replace ev = 1 if duplicate == 1
replace pm = 1 if duplicate == 1
duplicates drop date cformo if ev == 1 | pm == 1, force
* 1 observation deleted
drop duplicate

* Merge observations with meetings on same day, one in the morning and one in the evening
gsort date cformo cnum -slot  
duplicates report date cformo if am == 1 | ev == 1
duplicates tag date cformo if am == 1 | ev == 1, gen(duplicate)
list date slot cformo description cnum ev am pm if duplicate == 1
replace slot = 6 if duplicate == 1
replace ev = 1 if duplicate == 1
replace am = 1 if duplicate == 1
duplicates drop date cformo if ev == 1 | am == 1, force
* 1 observation deleted
drop duplicate

* Merge observations with meetings on same day, one in the morning and one in the afternoon
gsort date cformo cnum -slot  
duplicates report date cformo if am == 1 | pm == 1
duplicates tag date cformo if am == 1 | pm == 1, gen(duplicate)
list date slot cformo description cnum ev am pm if duplicate == 1
replace slot = 4 if duplicate == 1
replace am = 1 if duplicate == 1
replace pm = 1 if duplicate == 1
duplicates drop date cformo if am == 1 | pm == 1, force
* 95 observations deleted
drop duplicate

* Check for remaining duplicates
duplicates report date cformo
tab slot am, m
tab slot pm, m
tab slot ev, m

* Generate meeting time variable
generate dur = .
replace dur = am + pm + ev
label var dur "Meeting duration"
tab dur, m
tab slot dur, m

* Generate year variable
generate year = year(date)
label var year "Meeting year"
tab year, m
by cformo, sort: tab year

* Generate month variable
generate month = month(date)
label var month "Meeting month"
label def monthl 1 "January" 2 "February" 3 "March" 4 "April" 5 "May" 6 "June" /*
	*/ 7 "July" 8 "August" 9 "September" 10 "October" 11 "November" 12 "December"
label val month monthl
tab month, m
by cformo, sort: tab month
by year cformo, sort: tab month

* Generate week of year variable
generate week = week(date)
label var week "Meeting week"
tab week, m

* Generate day of week variable
generate day = dow(date)
label var day "Meeting day"
label define dayl 0 "Sunday" 1 "Monday" 2 "Tuesday" 3 "Wednesday" /*
	*/ 4 "Thursday" 5 "Friday" 6 "Saturday"
label val day dayl
tab day, m
by cformo, sort: tab day, m

* Generat semester variable
generate semester = halfyear(date)
label var semester "Meeting semester"
label def semesterl 1 "First semester" 2 "Second semester"
label val semester semesterl
tab semester, m
by cformo, sort: tab semester, m
tab week semester, m

* Generate presidency variable
generate pres = .
replace pres = 1 if year == 1995 & month <= 6
replace pres = 2 if year == 1995 & month > 6
replace pres = 3 if year == 1996 & month <= 6
replace pres = 4 if year == 1996 & month > 6
replace pres = 5 if year == 1997 & month <= 6
replace pres = 6 if year == 1997 & month > 6
replace pres = 7 if year == 1998 & month <= 6
replace pres = 8 if year == 1998 & month > 6
replace pres = 9 if year == 1999 & month <= 6
replace pres = 10 if year == 1999 & month > 6
replace pres = 11 if year == 2000 & month <= 6
replace pres = 12 if year == 2000 & month > 6
replace pres = 13 if year == 2001 & month <= 6
replace pres = 14 if year == 2001 & month > 6
replace pres = 15 if year == 2002 & month <= 6
replace pres = 16 if year == 2002 & month > 6
replace pres = 17 if year == 2003 & month <= 6
replace pres = 18 if year == 2003 & month > 6
replace pres = 19 if year == 2004 & month <= 6
replace pres = 20 if year == 2004 & month > 6
replace pres = 21 if year == 2005 & month <= 6
replace pres = 22 if year == 2005 & month > 6
replace pres = 23 if year == 2006 & month <= 6
replace pres = 24 if year == 2006 & month > 6
replace pres = 25 if year == 2007 & month <= 6
replace pres = 26 if year == 2007 & month > 6
replace pres = 27 if year == 2008 & month <= 6
replace pres = 28 if year == 2008 & month > 6
replace pres = 29 if year == 2009 & month <= 6
replace pres = 30 if year == 2009 & month > 6
replace pres = 31 if year == 2010 & month <= 6
replace pres = 32 if year == 2010 & month > 6
replace pres = 33 if year == 2011 & month <= 6
replace pres = 34 if year == 2011 & month > 6
replace pres = 35 if year == 2012 & month <= 6
replace pres = 36 if year == 2012 & month > 6
replace pres = 37 if year == 2013 & month <= 6
replace pres = 38 if year == 2013 & month > 6
replace pres = 39 if year == 2014 & month <= 6
replace pres = 40 if year == 2014 & month > 6
label var pres "Presidency"

* Define labels for presidency variable
label def presl /*
	*/ 1 "FR-1-95" /*
	*/ 2 "ES-2-95" /*
	*/ 3 "IT-1-96" /*
	*/ 4 "IE-2-96" /*
	*/ 5 "NL-1-97" /*
	*/ 6 "LU-2-97" /*
	*/ 7 "UK-1-98" /*
	*/ 8 "AT-2-98" /*
	*/ 9 "DE-1-99" /*
	*/ 10 "FI-2-99" /*
	*/ 11 "PT-1-00" /*
	*/ 12 "FR-2-00" /*
	*/ 13 "SE-1-01" /*
	*/ 14 "BE-2-01" /*
	*/ 15 "ES-1-02" /*
	*/ 16 "DK-2-02" /*
	*/ 17 "EL-1-03" /*
	*/ 18 "IT-2-03" /*
	*/ 19 "IE-1-04" /*
	*/ 20 "NL-2-04" /*
	*/ 21 "LU-1-05" /*
	*/ 22 "UK-2-05" /*
	*/ 23 "AT-1-06" /*
	*/ 24 "FI-2-06" /*
	*/ 25 "DE-1-07" /*
	*/ 26 "PT-2-07" /*	
	*/ 27 "SI-1-08" /*
	*/ 28 "FR-2-08" /*
	*/ 29 "CZ-1-09" /*
	*/ 30 "SE-2-09" /*
	*/ 31 "ES-1-10" /*
	*/ 32 "BE-2-10" /*
	*/ 33 "HU-1-11" /*
	*/ 34 "PL-2-11" /*
	*/ 35 "DK-1-12" /*
	*/ 36 "CY-2-12" /*
	*/ 37 "IE-1-13" /*
	*/ 38 "LT-2-13" /*
	*/ 39 "EL-1-14" /*
	*/ 40 "IT-2-14", modify	
label val pres presl
tab pres, m

* Generate generic Council formation variable
tostring(cformo), generate(cform1) 
generate cforma = real(substr(cform1, 1, 1))
tab cformo cforma, m
label var cforma "Council formation (aggregated)"
drop cform1

* Define value labels for Council formation variable
label def cformal /*
	*/ 1 "Education, Youth, and Culture" /*
	*/ 2 "Agriculture and Fisheries" /*
	*/ 3 "Economic and Financial Affairs" /*
	*/ 4 "General Affairs and External Relations" /*
	*/ 5 "Environment" /*
	*/ 6 "Justice and Home Affairs" /*
	*/ 7 "Transport, Telecommunications, and Energy" /*
	*/ 8 "Competitiveness" /*
	*/ 9 "Employment, Social Policy, Health, and Consumer Affairs" /*
	*/ , modify
label val cforma cformal
tab cforma, m
tab cformo cforma, m
tab cformo cforma, m nol

* Aggregation poses two problems: 
* Consumer Affairs pre-2002 is included in Competitiveness, but is later actually part of Employment,
* Social Policy, Consumer Protection and Health
* Industry pre-2002 is included in Transport, Telecommunications, and Energy, but is later actually part
* of Competitiveness
	
	
* Generate alternative versions of aggregated Council formation variable
************************************************************************

* Recode Energy, but not Consumer Affairs
generate cforma2 = cforma
label var cforma2 "Council formation 2 (aggregated)"
label val cforma2 cformal
* Recode Industry and Energy to Competitiveness rather than TTE
replace cforma2 = 8 if cformo == 712	
* Recode Industry to Competitiveness rather than TTE
replace cforma2 = 8 if cformo == 723	
tab cformo cforma2, m

* Recode Consumer Affairs, but not Energy
generate cforma3 = cforma
label var cforma3 "Council formation 3 (aggregated)"
label val cforma3 cformal
* Recode Internal Market, Consumer Affairs,and Tourism to Employment, Social Policy, Health, and Consumer
* Affairs rather than Competitiveness
replace cforma3 = 9 if cformo == 811	
* Recode Consumer Affairs to Employment, Social Policy, Health, and Consumer Affairs rather than Competitiveness
replace cforma3 = 9 if cformo == 823		
tab cformo cforma3, m	

* Recode both Consumer Affairs and Energy
generate cforma4 = cforma
label var cforma4 "Council formation 4 (aggregated)"
label val cforma4 cformal
* Recode Industry and Energy to Competitiveness rather than TTE
replace cforma4 = 8 if cformo == 712	
* Recode Industry to Competitiveness rather than TTE
replace cforma4 = 8 if cformo == 723	
* Recode Internal Market, Consumer Affairs,and Tourism to Employment, Social Policy, Health, and Consumer
* Affairs rather than Competitiveness
replace cforma4 = 9 if cformo == 811	
* Recode Consumer Affairs to Employment, Social Policy, Health, and Consumer Affairs rather than Competitiveness
replace cforma4 = 9 if cformo == 823		
tab cformo cforma4, m


* Sort and order data
sort date
order period date slot dur cformo cforma cnum am pm ev year month week day semester pres

* Drop processed meeting description variable
drop description
rename description_orig description
label var description "Meeting description"

* Save meeting level dataset
compress
des, s
save "Output data\wpar-management02-council01-individual.dta", replace


*****************************************************************************************
* Generate semester-level datasets according to different formation aggregation variables
*****************************************************************************************

* Collapse to semester data according to first Council formation variable
preserve
collapse (sum) dur, by(cforma year semester)
label var dur "Number of sessions"

* Generate Council formation indicator variable
generate cformv = 1
label var cformv "Council formation variable"
label def cformvl /*
	*/ 1 "Default coding" /*
	*/ 2 "Consumer Affairs recoded" /*
	*/ 3 "Industry recoded" /*
	*/ 4 "Consumer Affairs and Industry recoded" /*
	*/ , modify
label val cformv cformvl

* Save dataset
sort cforma year semester
order cformv year semester dur cforma
des, s
save "Output data\wpar-management02-council02-aggregate01.dta", replace
restore

* Collapse to semester data according to second Council formation variable
preserve
collapse (sum) dur, by(cforma2 year semester)
label var dur "Number of sessions"
rename cforma2 cforma 

* Generate Council formation indicator variable
generate cformv = 2
label var cformv "Council formation variable"
label def cformvl /*
	*/ 1 "Default coding" /*
	*/ 2 "Consumer Affairs recoded" /*
	*/ 3 "Industry recoded" /*
	*/ 4 "Consumer Affairs and Industry recoded" /*
	*/ , modify
label val cformv cformvl

* Save dataset
sort cforma year semester
order cformv year semester dur cforma
des, s
save "Output data\wpar-management02-council02-aggregate02.dta", replace
restore

* Collapse to semester data according to third Council formation variable
preserve
collapse (sum) dur, by(cforma3 year semester)
label var dur "Number of sessions"
rename cforma3 cforma 

* Generate Council formation indicator variable
generate cformv = 3
label var cformv "Council formation variable"
label def cformvl /*
	*/ 1 "Default coding" /*
	*/ 2 "Consumer Affairs recoded" /*
	*/ 3 "Industry recoded" /*
	*/ 4 "Consumer Affairs and Industry recoded" /*
	*/ , modify
label val cformv cformvl

* Save dataset
sort cforma year semester
order cformv year semester dur cforma
des, s
save "Output data\wpar-management02-council02-aggregate03.dta", replace
restore

* Collapse to semester data according to fourth Council formation variable
collapse (sum) dur, by(cforma4 year semester)
label var dur "Number of sessions"
rename cforma4 cforma 

* Generate Council formation indicator variable
generate cformv = 4
label var cformv "Council formation variable"
label def cformvl /*
	*/ 1 "Default coding" /*
	*/ 2 "Consumer Affairs recoded" /*
	*/ 3 "Industry recoded" /*
	*/ 4 "Consumer Affairs and Industry recoded" /*
	*/ , modify
label val cformv cformvl

* Save dataset
sort cforma year semester
order cformv year semester dur cforma
des, s
save "Output data\wpar-management02-council02-aggregate04.dta", replace

* Append other datasets
append using "Output data\wpar-management02-council02-aggregate01.dta"
append using "Output data\wpar-management02-council02-aggregate02.dta"
append using "Output data\wpar-management02-council02-aggregate03.dta"
sort cformv year semester
tab cformv, m
tab year cformv, m

* Generate semester of year variable
generate dates = yh(year, semester)
label var dates "Semester of year"
format dates %thh-CCYY
drop year semester
order cformv dates

* Save dataset
compress
des, s
save "Output data\wpar-management02-council03-aggregateAll.dta", replace

* Close log
log close
exit
